package pl.soulmusic.phylactery.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseOpenHelper extends SQLiteOpenHelper {

	public static final String TAG = "SoulMusicDAO";
	private static final int DATABASE_VERSION = 21;
	private static final String DATABASE_NAME = "pl.soulmusic.phylactery.db";
	
	// player tables 
	public static final String PLAYLIST_TABLE = "playlist";
	public static final String PROPERTIES_TABLE = "properties";
	
	public static final String NAME_COLUMN = "name";
	public static final String POSITION_COLUMN = "position";
	public static final String SONG_ID_COLUMN = "song_id";
	public static final String VALUE_COLUMN = "value";
	
	public static final String CURRENT_SONG_PROPERTY = "current_song";
	public static final String NEXT_SONG_PROPERTY = "next_song";
	
	private static final String PLAYLIST_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + PLAYLIST_TABLE + " (" +
            POSITION_COLUMN + " INT, " +
            SONG_ID_COLUMN +" INT);";
	private static final String PROPERTIES_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + PROPERTIES_TABLE + " (" +
            NAME_COLUMN + " TEXT, " +
            VALUE_COLUMN + " TEXT);";

	// location tables
	public static final String TRACKS_TABLE = "tracks";
	public static final String POIS_TABLE = "pois";
	public static final String POI_TRACKS_TABLE = "poi_tracks";
	
	private static final String TRACKS_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + TRACKS_TABLE + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "song_id INT," +
            "latitude INT,"+
            "longitude INT," +
            "loggedTime INT"+");";
	
	private static final String POIS_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + POIS_TABLE + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "name TEXT, " +
            "description" + " TEXT, " +
            "latitude INT,"+
            "longitude INT,"+
            "range INT" +");";
	
	private static final String POI_TRACKS_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + POI_TRACKS_TABLE + " (" +
            "track_id INT REFERENCES tracks(id)," +
            "poi_id INT REFERENCES pois(id)"+ ");";	
	
	
	private static final String DROP_ALL =
			"DROP TABLE " + PLAYLIST_TABLE + ";" +
			"DROP TABLE " + PROPERTIES_TABLE + ";" +
			"DROP TABLE " + POI_TRACKS_TABLE + ";" +
			"DROP TABLE " + TRACKS_TABLE + ";" +
			"DROP TABLE " + POIS_TABLE + ";";
	
	public DatabaseOpenHelper(Context context,
			CursorFactory factory) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(PROPERTIES_TABLE_CREATE);
		db.execSQL(PLAYLIST_TABLE_CREATE);
		
		db.execSQL(TRACKS_TABLE_CREATE);
		db.execSQL(POIS_TABLE_CREATE);
		db.execSQL(POI_TRACKS_TABLE_CREATE);
		
		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.NAME_COLUMN, DatabaseOpenHelper.NEXT_SONG_PROPERTY);
		values.put(DatabaseOpenHelper.VALUE_COLUMN, 0);
		db.insert(PROPERTIES_TABLE, null, values);
		values.clear();
		values.put(DatabaseOpenHelper.NAME_COLUMN, DatabaseOpenHelper.CURRENT_SONG_PROPERTY);
		values.put(DatabaseOpenHelper.VALUE_COLUMN, 0);
		db.insert(PROPERTIES_TABLE, null, values);
		values.clear();
		values.put(DatabaseOpenHelper.NAME_COLUMN, "followGPS");
		values.put(DatabaseOpenHelper.VALUE_COLUMN, 0);
		db.insert(PROPERTIES_TABLE, null, values);		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(DROP_ALL);
		Log.i(TAG, "Dropping DB and recreating.");
		onCreate(db);
	}

}
